package com.zz.ecommerce.menu;

import com.zz.ecommerce.mybatis.handler.EmptyStringIfNullHandler;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 菜单dao
 *
 * @author
 */
@Repository
public interface MenuDao {

    /**
     * @return
     */
    @Select("SELECT INDX,ID,PID,MENUNAME,URL,ISENABLED,MENUICON FROM SYS_MENU_INFO_JN t WHERE t.ISENABLED='1' AND t.PORTALFLAG = #{flag} " +
            "ORDER BY t.ID, INDX ")
    @Results({
            @Result(column = "URL", property = "url", jdbcType = JdbcType.VARCHAR,
                    typeHandler = EmptyStringIfNullHandler.class)
    })
    List<MenuInfo> queryAllMenus(@Param("flag") String flag);

    @Select("SELECT INDX,ID,PID,MENUNAME,URL,ISENABLED,MENUICON FROM SA_MENU_INFO t WHERE t.ISENABLED='1' " +
            "ORDER BY t.INDX ")
    @Results({
            @Result(column = "URL", property = "url", jdbcType = JdbcType.VARCHAR,
                    typeHandler = EmptyStringIfNullHandler.class)
    })
    List<MenuInfo> queryAllMenusF();

    @Select("SELECT COUNT(1) FROM T_ENTRYINFO WHERE ENTRYTY = 'I' ")
    String getCustImp();

    @Select("SELECT COUNT(1) FROM T_ENTRYINFO WHERE ENTRYTY = 'O'")
    String getCustExp();

    @Select("SELECT COUNT(1) FROM T_DECL_INFO WHERE IO = 'I'")
    String getInspImp();

    @Select("SELECT COUNT(1) FROM T_DECL_INFO WHERE IO = 'O'")
    String getInspExp();

    @Select("SELECT COUNT(1) " +
            "  FROM T_WORKSHEET T " +
            " WHERE T.BUSINESSTYPE_Z = 'I' " +
            "   AND T.MAKEDATE_Z BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1) AND " +
            "       TRUNC(SYSDATE, 'mm')")
    int getMonImp();

    @Select("SELECT COUNT(1) " +
            "  FROM T_WORKSHEET T " +
            " WHERE T.BUSINESSTYPE_Z = 'I' " +
            "   AND T.MAKEDATE_Z BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1) AND " +
            "       TRUNC(SYSDATE, 'mm')")
    int getLLMonImp();

    @Select("SELECT COUNT(1) " +
            "  FROM T_WORKSHEET T " +
            " WHERE T.BUSINESSTYPE_Z = 'O' " +
            "   AND T.MAKEDATE_Z BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1) AND " +
            "       TRUNC(SYSDATE, 'mm')")
    int getMonExp();

    @Select("SELECT COUNT(1) " +
            "  FROM T_WORKSHEET T " +
            " WHERE T.BUSINESSTYPE_Z = 'O' " +
            "   AND T.MAKEDATE_Z BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -2) AND " +
            "       ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)")
    int getLLMonExp();

    @Select(
            "SELECT COUNT(1) FROM T_CUST_RELEASEINFO t WHERE t.receive_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1) " +
                    "AND  TRUNC(SYSDATE, 'mm')")
    int getRelease();

    @Select(
            "SELECT COUNT(1) FROM T_CUST_RELEASEINFO t WHERE t.receive_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -2) " +
                    "AND  ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)")
    int getLLRelease();

    @Select(" SELECT ENTRYTY||'C' AS TYPE,COUNT(1)||'' AS COUNT" +
            "  FROM CARGO.T_ENTRYINFO" +
            " WHERE CREATETIME >TRUNC(SYSDATE, 'MM') AND ORGANIZATION_ID=#{organizationId} GROUP BY ENTRYTY" +
            " UNION ALL" +
            " SELECT APL_KIND||'I',COUNT(1)||''  AS COUNT " +
            "  FROM CARGO.T_DECL_INFO" +
            " WHERE CREATETIME >TRUNC(SYSDATE, 'MM') AND ORGANIZATION_ID=#{organizationId} GROUP BY APL_KIND")
    List<Map<String, String>> getHeaderData(String organizationId);

    @Select(" SELECT T.BUSINESSTYPE_Z AS TYPE, COUNT(1)||''  AS COUNT" +
            "   FROM cargo.T_WORKSHEET T" +
            "  WHERE (T.MAKEDATE_Z BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1) AND" +
            "        TRUNC(SYSDATE, 'mm')) AND ORGANIZATION_ID_Z=#{organizationId} " +
            "  GROUP BY T.BUSINESSTYPE_Z" +
            " UNION ALL" +
            " SELECT T.BUSINESSTYPE_Z||'L', count(1)||'' AS COUNT" +
            "   FROM CARGO.T_WORKSHEET T" +
            "  WHERE (T.MAKEDATE_Z BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -2) AND" +
            "        ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)) AND ORGANIZATION_ID_Z=#{organizationId} " +
            "  GROUP BY T.BUSINESSTYPE_Z")
    List<Map<String, String>> getFooterData(String organizationId);


    @Select(
            " SELECT COUNT(1) AS COUNT, FROM T_CUST_RELEASEINFO t " +
                    "WHERE t.receive_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'), -1) " +
                    "AND  TRUNC(SYSDATE, 'mm') " +
                    "UNION  ALL " +
                    "SELECT COUNT(1) FROM T_CUST_RELEASEINFO t WHERE t.receive_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -2) " +
                    "AND  ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)")
    List<Map<String, String>> getReleaseData();

}
